SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[spshowcontigall] AS
SET NOCOUNT ON
DECLARE @tablename varchar(128)
DECLARE @id int
DECLARE @indid smallint
DECLARE @indname varchar(126)
DECLARE @tablename_header varchar(255)
DECLARE tnames_cursor CURSOR 

FOR

SELECT obj.name, obj.id, idx.indid, idx.name
    FROM sysobjects AS obj JOIN sysindexes AS idx
    ON obj.id = idx.id
    WHERE type = 'U' AND uid = 1 AND indid < 255

OPEN tnames_cursor

FETCH NEXT FROM tnames_cursor INTO @tablename, @id, @indid, @indname

WHILE (@@fetch_status <> -1)
BEGIN

   IF (@@fetch_status <> -2)
   BEGIN

      IF @indid=0
         SELECT @tablename_header = 'Checking fragmentation on Table '+ RTRIM(UPPER(@tablename))

      IF @indid=1
         SELECT @tablename_header = 'Checking fragmentation on Clustered Index '+ RTRIM(UPPER(@tablename))

      IF @indid >1
         SELECT @tablename_header = 'Checking fragmentation on Non-clustered Index '+ RTRIM(UPPER(@indname))+' on Table '+RTRIM(UPPER(@tablename))

      PRINT ' '
      PRINT @tablename_header

      SELECT @id = OBJECT_ID(@tablename)
      DBCC SHOWCONTIG (@id, @indid)

   END
   FETCH NEXT FROM tnames_cursor INTO @tablename, @id, @indid, @indname
END

PRINT ' '
PRINT ' '

SELECT @tablename_header = '************ NO MORE TABLES ************'
PRINT @tablename_header

PRINT ' '
PRINT 'Fragmentation has been checked for all tables and indexes.'

DEALLOCATE tnames_cursor
GO
